﻿namespace MSSQL.Implement
{
    using Interface;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using Dapper;
    using CommandTool;
    using System;
    using System.Reflection;
    using ToolsModel;
    using ComTools.Extensions;
    using ComTools.File;

    /// <summary>
    /// 该类实现了数据的更新操作
    /// </summary>
    public class ExcuteData : AbstractDataExcute
    {

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="connection">链接对象</param>
        /// <param name="transaction">事务</param>
        /// <param name="sql">sql</param>
        /// <returns>执行的结果</returns>
        public override int ExcuteTransactionImpl(IDbConnection connection, IDbTransaction transaction, string sql, object param = null, int? commandTimeOut = 10, CommandType commandType = CommandType.Text)
        {
            int ret = connection.Execute(sql, param, transaction, commandTimeOut, commandType);

            if (ret > 0)
            {
                transaction.Commit();
            }
            else
            {
                transaction.Rollback();
            }
            return ret;
        }


        /// <summary>
        /// 插入多条数据
        /// </summary>
        /// <typeparam name="T">插入的类型</typeparam>
        /// <param name="connection">数据库链接</param>
        /// <param name="transaction">事务</param>
        /// <param name="t">要插入的数据</param>
        /// <returns>插入结果</returns>
        public override int InsertMultipleImpl<T>(IDbConnection connection, IDbTransaction transaction, IList<T> t)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(XmlConverter.ToXml(t.ToList()));

            int row = connection.Execute(sb.ToString(), null, transaction, null, CommandType.Text);

            if (row > 0)
            {
                transaction.Commit();
            }
            else
            {
                transaction.Rollback();
            }
            return row;
        }

        /// <summary>
        /// 插入单条数据(传入实体类获取插入SQL)
        /// </summary>
        /// <typeparam name="T">插入的类型</typeparam>
        /// <param name="connection">数据库链接</param>
        /// <param name="t">要插入的数据</param>
        /// <returns>插入结果</returns>
        public override int InsertSingleDataImpl<T>(IDbConnection connection, T insertObject)
        {
            string sql = string.Empty;
            int ret = 0;
            try
            {
                string sqlCommandStart = "INSERT INTO [{0}]";       // 
                StringBuilder mySQL = new StringBuilder();          // 生成sql的构建器
                List<string> fieldNameArr = new List<string>();     // 装字段名的集合
                List<string> fieldValueArr = new List<string>();    // 装字段值的集合
                PropertyInfo[] properties =
                    insertObject.GetType().GetProperties();         // 对象的属性数组
                var tableName = insertObject.GetType().
                        GetCustomAttribute<TableInfo>().
                        TableName;                                  //数据表名称

                // 添加表名
                mySQL.Append(sqlCommandStart.FormatString(tableName));

                foreach (var property in properties)
                {

                    var customerFieldInfoAttr = property.GetCustomAttribute<ToolsModel.FieldInfo>();    // 字段对应的数据信息
                    var insertObjectFieldValue = property.GetValue(insertObject);                           // 获取传入对象字段的值
                    var value = insertObjectFieldValue != null ? insertObjectFieldValue.ToString() : "";    // 要插入的值，没有值就不插入，这个值是根据字段值来的

                    if (!string.IsNullOrWhiteSpace(value))
                    {
                        // 获取字段属性类型的名称
                        var pName = property.PropertyType.FullName.ToLower();

                        if (pName.IndexOf("list") < 0)
                        {
                            //加入字段名称
                            fieldNameArr.Add("[{0}]".FormatString(customerFieldInfoAttr.ColumnName));

                            //加入value值
                            if (pName.IndexOf("guid") >= 0 || pName.IndexOf("datetime") >= 0 || pName.IndexOf("string") >= 0)
                            {
                                fieldValueArr.Add(string.Format("'{0}'", value));
                            }
                            else if (pName.IndexOf("int") >= 0 || pName.IndexOf("byte") >= 0 || pName.IndexOf("decimal") >= 0)
                            {
                                fieldValueArr.Add(string.Format("{0}", value));
                            }
                            else if (pName.IndexOf("boolean") >= 0)
                            {
                                fieldValueArr.Add(string.Format("{0}", value.ToLower() == "false" ? "0" : "1"));
                            }
                        }
                    }
                }

                sql = mySQL.Append(
                    "({0}) VALUES({1});".FormatString(
                        string.Join(",", fieldNameArr),
                        string.Join(",", fieldValueArr))).
                    ToString();

                ret = connection.Execute(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ret;
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="connection">数据库链接</param>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数，匿名类型，如new {loginName = "123"}，对应的是语句中的sql参数，如：update…… where [sLoginName] = @loginName;请注意匿名类型属性名称和参数名称保持一致</param>
        /// <returns>更新结果</returns>
        public override int UpdateImpl(IDbConnection connection, IDbTransaction transaction, string sql, object param)
        {
            CommandDefinition command = new CommandDefinition(sql, param, transaction, 30, CommandType.Text, CommandFlags.NoCache);
            var ret = connection.Execute(command);

            if (ret > 0)
            {
                transaction.Commit();
            }
            else
            {
                transaction.Rollback();
            }

            return ret;
        }

        /// <summary>
        /// 更新单个数据(传入实体类获取更新SQL)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="connection">链接</param>
        /// <param name="t">要更新的数据</param>
        /// <param name="where">条件</param>
        /// <returns>更新结果</returns>
        public override int UpdateSingleImpl<T>(IDbConnection connection, T t, string where)
        {
            string sql = string.Empty;
            int ret = 0;
            try
            {
                StringBuilder mySQL = new StringBuilder();          // 生成sql的构建器
                List<string> setStrArray = new List<string>();      // 更新值的set集合
                PropertyInfo[] pros = t.GetType().GetProperties();  // 属性集合

                var tbName = t.GetType().GetCustomAttribute<TableInfo>().TableName;  // 表名

                mySQL.Append(string.Format("UPDATE [{0}] SET ", tbName));

                foreach (var pro in pros)
                {
                    var field = pro.GetCustomAttribute<ToolsModel.FieldInfo>();// 字段对应的数据信息
                    var fvalue = pro.GetValue(t);                              // 获取传入对象字段的值
                    var value = fvalue != null ? fvalue.ToString() : "";       // 更新的值，没有值就不更新，这个值是根据字段值来的

                    if (!string.IsNullOrEmpty(value) && !string.IsNullOrWhiteSpace(value))
                    {
                        var pName = pro.PropertyType.FullName.ToLower(); // 字段名称

                        if (field != null && field.ColumnName.ToLower() != "id" && field.ColumnName.ToLower() != "gcode")
                        {
                            if (pName.IndexOf("guid") >= 0 || pName.IndexOf("datetime") >= 0 || pName.IndexOf("string") >= 0)
                            {
                                setStrArray.Add(string.Format("[{0}] = '{1}'", field.ColumnName, value));
                            }
                            else if (pName.IndexOf("int") >= 0 || pName.IndexOf("byte") >= 0 || pName.IndexOf("decimal") >= 0)
                            {
                                setStrArray.Add(string.Format("[{0}] = {1}", field.ColumnName, value));
                            }
                            else if (pName.IndexOf("boolean") >= 0)
                            {
                                setStrArray.Add(string.Format("[{0}] = {1}", field.ColumnName, value.ToLower() == "true" ? 1 : 0));
                            }
                        }
                    }
                }
                sql = mySQL.AppendFormat("{0}{1}", string.Join(",", setStrArray), string.Format("{0}{1};", " where ", where)).ToString();
                ret = connection.Execute(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ret;
        }


        /// <summary>
        /// 更新单个数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="connection">链接</param>
        /// <param name="t">要更新的数据</param>
        /// <returns></returns>
        public override int UpdateSingleImpl<T>(IDbConnection connection, T t)
        {

            int ret = 0;
            try
            {
                StringBuilder mySQL = new StringBuilder();          // 生成sql的构建器
                List<string> setStrArray = new List<string>();      // 更新值的set集合
                PropertyInfo[] pros = t.GetType().GetProperties();  // 属性集合
                var tbName = t.GetType().GetCustomAttribute<TableInfo>().TableName;      // 表名

                mySQL.Append(string.Format("UPDATE [{0}] SET ", tbName));

                string where = string.Empty;

                foreach (var pro in pros)
                {

                    var field = pro.GetCustomAttribute<ToolsModel.FieldInfo>();// 字段对应的数据信息
                    var fvalue = pro.GetValue(t);                                  // 获取传入对象字段的值
                    var value = fvalue != null ? fvalue.ToString() : "";           // 更新的值，没有值就不更新，这个值是根据字段值来的

                    if (!string.IsNullOrEmpty(value) && !string.IsNullOrWhiteSpace(value))
                    {
                        var pName = pro.PropertyType.FullName.ToLower(); // 字段名称

                        if (field != null)
                        {
                            if (field.ColumnName.ToLower() != "id" && field.ColumnName.ToLower() != "gcode")
                            {
                                if (pName.IndexOf("guid") >= 0 || pName.IndexOf("datetime") >= 0 || pName.IndexOf("string") >= 0)
                                {
                                    setStrArray.Add(string.Format("[{0}] = '{1}'", field.ColumnName, value));
                                }
                                else if (pName.IndexOf("int") >= 0 || pName.IndexOf("byte") >= 0 || pName.IndexOf("decimal") >= 0)
                                {
                                    setStrArray.Add(string.Format("[{0}] = {1}", field.ColumnName, value));
                                }
                                else if (pName.IndexOf("boolean") >= 0)
                                {
                                    setStrArray.Add(string.Format("[{0}] = {1}", field.ColumnName, value.ToLower() == "true" ? 1 : 0));
                                }
                            }
                            else
                            {
                                where = "[" + field.ColumnName.ToLower() + "] =  '" + value + "'";
                            }
                        }
                    }
                }
                mySQL.AppendFormat("{0}{1}", string.Join(",", setStrArray), string.Format("{0}{1};", " where ", where));

                ret = connection.Execute(mySQL.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ret;
        }
    }
}
